﻿using Microsoft.AspNetCore.DataProtection.KeyManagement;
using bpms.Models;
using MySql.Data.MySqlClient;

namespace bpms.Dao
{
    /// <summary>
    /// 订单数据持久层
    /// </summary>
    public class OrderDao
    {
        /// <summary>
        /// 获取所有订单
        /// </summary>
        /// <returns></returns>
        public List<OrderView> GetAll(string key)
        {

            var sql = $@"
                SELECT o.*, p.product_no, p.name as product_name, b.name as customer_name
                FROM `order` o
                join product p on o.product_id=p.Id
                join customer b on o.customer_id=b.id
                where 1=1";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and (o.order_no like CONCAT('%', @key, '%') or p.name like CONCAT('%', @key, '%')";
            }
            sql += " order by o.id desc";
            var orderList = MySqlDbHelper.QueryList<OrderView>(sql, new MySqlParameter("@key", key));
            return orderList;
        }

        /// <summary>
        /// 获取指定顾客的所有订单
        /// </summary>
        /// <param name="buyerId"></param>
        /// <returns></returns>
        public List<OrderView> GetBuyerAll(int customerId, string key)
        {
            var sql = $@"
                 SELECT o.*, p.product_no, p.name as product_name, b.name as customer_name
                 FROM `order` o
                join product p on o.product_id=p.Id
                join customer b on o.customer_id=b.id
                where o.customer_id=@customerId";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and (o.order_no like CONCAT('%', @key, '%') or p.name like CONCAT('%', @key, '%')";
            }
            sql += " order by o.id desc";
            var orderList = MySqlDbHelper.QueryList<OrderView>(sql,
                new MySqlParameter("@customerId", customerId),
                new MySqlParameter("@key", key)
                );
            return orderList;
        }

        /// <summary>
        /// 获取指定订单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public OrderView GetById(int id)
        {
            var order = MySqlDbHelper.Query<OrderView>(
                @"SELECT o.*, p.product_no, p.name as product_name, b.name as customer_name
                 FROM `order` o
                join product p on o.product_id=p.Id
                join customer b on o.customer_id=b.id
                where o.id=@Id", new MySqlParameter("@id", id)
                );
            return order;
        }

        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public int Add(Order order)
        {
            var sql = @"
                    INSERT INTO `order`(order_no, quantity, customer_id, product_id, delivery_time, total_amount, created_time)
                    VALUES(@order_no, @quantity, @customer_id, @product_id, @delivery_time, @total_amount, now());";
            var parameters = new[] {
                new MySqlParameter("@product_id", order.ProductID),
                new MySqlParameter("@customer_id", order.CustomerID),
                new MySqlParameter("@quantity", order.Quantity),
                new MySqlParameter("@total_amount", order.TotalAmount),
                new MySqlParameter("@order_no", order.OrderNo),
                new MySqlParameter("@delivery_time", order.DeliveryTime)
            };
            return MySqlDbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 删除订单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            var sql = "delete from `order` where id=@id";
            return MySqlDbHelper.Execute(sql, new MySqlParameter("@id", id));
        }
    }
}
